package org.springboot.szsrcb.srvice;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springboot.szsrcb.entity.Req_base_info;
import org.springboot.szsrcb.entity.Req_bill;
import org.springboot.szsrcb.entity.Req_team_info_log;
import org.springboot.szsrcb.entity.Req_work_log;
import org.springboot.szsrcb.entity.Req_workload_info;
import org.springboot.szsrcb.entity.Test_work_log;
import org.springboot.szsrcb.srvice.Test_bill_Service.Test_work_logRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

@Service
public class Req_bill_Service {
	private static final Logger logger = LoggerFactory.getLogger(Req_bill_Service.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private Att_Service att_Service;

	/*public List<Req_bill> getList() {
		String sql = "SELECT ID,NAME FROM req_bill";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		
		 * @Override public User mapRow(ResultSet rs, int rowNum) throws
		 * SQLException { User u = new User(); u.setId(rs.getInt("ID"));
		 * u.setName(rs.getString("NAME")); return u; }
		 * 
		 * });
		 
		return (l_q);
	}*/

	public List<Req_bill> get_All_Req() {
		String sql = "SELECT * FROM req_bill ORDER BY id DESC";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		/*
		 * @Override public User mapRow(ResultSet rs, int rowNum) throws
		 * SQLException { User u = new User(); u.setId(rs.getInt("ID"));
		 * u.setName(rs.getString("NAME")); return u; }
		 * 
		 * });
		 */
		return (l_q);
	}
	
	
	public boolean save_req_bill(Req_bill req_bill) {
		/*
		 * String sql =
		 * "INSERT INTO req_bill 	(req_bill_no,req_status,req_bill_original_no,req_title,req_type,req_offer_dept,req_offer,required_completion_date,deffect_no,accept_date,attach_group_id,req_bill_create_date,req_bill_modify_date)	"
		 * + "VALUES (\'" + req_bill.getReq_bill_no() + "\',\'已接收\' ,\'" +
		 * req_bill.getReq_bill_original_no() + "\',\'" +
		 * req_bill.getReq_title() + "\', \'" + req_bill.getReq_type() + "\',\'"
		 * + req_bill.getReq_offer_dept() + "\',\'" + req_bill.getReq_offer() +
		 * "\'," + req_bill.getRequired_completion_date() + ",\'" +
		 * req_bill.getDeffect_no() + "\'," + req_bill.getAccept_date() + ", \'"
		 * + req_bill.getAttach_group_id() + "\', " +
		 * req_bill.getReq_bill_create_date() + ");";
		 */
		boolean return_r = false;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		final java.util.Date d = new java.util.Date();
		String set_creat_time = sdf2.format(d);
		
		Date sqlDate = new Date(req_bill.getAccept_date().getTime());
		String getDeffect_no = "";
		/*
		 * String sql =
		 * "INSERT INTO req_bill 	(req_bill_no,req_bill_original_no,req_status,req_title,req_type,req_offer_dept,req_offer,accept_date,required_completion_date,req_bill_create_date,deffect_no)VALUES (\'"
		 * + req_bill.getReq_bill_no() + "\',\'" +
		 * req_bill.getReq_bill_original_no() + "\',\'已接收\' ,\'" +
		 * req_bill.getReq_title() + "\', \'" + req_bill.getReq_type() + "\',\'"
		 * + req_bill.getReq_offer_dept() + "\',\'" + req_bill.getReq_offer() +
		 * "\',Date(\'" + sdf.format(req_bill.getAccept_date()) + "\'),Date(\'"
		 * + sdf.format(req_bill.getRequired_completion_date()) + "\'),Date(\'"
		 * + set_creat_time + "\'),\'" + req_bill.getDeffect_no() + "\');";
		 */

		// PreparedStatementCallback<?> action = null;

		// jdbcTemplate.execute(sql, action);
		try {

			// Integer get_r = jdbcTemplate.update(sql);
			String sql = "INSERT INTO req_bill (req_bill_no,req_bill_original_no,req_status,"
					+ "req_title,req_type,req_offer_dept,req_offer,accept_date,required_completion_date,"
					+ "req_bill_create_date,deffect_no)VALUES(?,?,?,?,?,?,?,?,?,?,?); ";
			logger.info("从数据库执行" + sql);

			/*
			 * if(req_bill.getDeffect_no()==null){ req_bill.setDeffect_no("");;
			 * }
			 * 
			 * if(req_bill.getReq_bill_original_no()==null){
			 * req_bill.setReq_bill_original_no(""); }
			 */

			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, req_bill.getReq_bill_no());
					ps.setString(2, req_bill.getReq_bill_original_no());
					ps.setString(3, "已接收");
					ps.setString(4, req_bill.getReq_title());
					ps.setString(5, req_bill.getReq_type());
					ps.setString(6, req_bill.getReq_offer_dept());
					ps.setString(7, req_bill.getReq_offer());
					ps.setDate(8, new java.sql.Date(req_bill.getAccept_date().getTime()));
					java.util.Date required_completion_date = req_bill.getRequired_completion_date();
					if (required_completion_date == null) {
						// ps.setDate(9, new java.sql.Date(0));
						ps.setNull(9, Types.DATE);
					} else {
						ps.setDate(9, new java.sql.Date(req_bill.getRequired_completion_date().getTime()));
						// ps.setTime(9, new
						// java.sql.Date(req_bill.getRequired_completion_date().getTime()));
					}
					try {
					java.util.Date date= sdf2.parse(set_creat_time);
					
					Timestamp timeStamp = new Timestamp(date.getTime());
					ps.setTimestamp(10, timeStamp);
					} catch (ParseException e) {
						
						e.printStackTrace();
					};
					
					ps.setString(11, req_bill.getDeffect_no());

				}
			};
			
			Integer get_r = jdbcTemplate.update(sql, pss);
			if(get_r==1){
				return_r = true;
			}
			if (get_r == 1 && req_bill.getAtt_id() != "") {
				String sql2 = "Select * FROM req_bill WHERE req_title = \'" + req_bill.getReq_title()
						+ "\' AND req_bill_no = \'" + req_bill.getReq_bill_no()
						+ "\' AND req_bill_create_date = '" + set_creat_time + "';";
				logger.info("sql2 :" + sql2);
				List<Req_bill> l_q = jdbcTemplate.query(sql2, new Req_billRowMapper());
				Req_bill r = l_q.get(0);
				logger.info("r id :" + r.getId() + " " + r.getReq_bill_no() + " " + r.getReq_title());
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("relation_req_id", r.getId().toString());
				map.put("att_ids", req_bill.getAtt_id());
				return_r=att_Service.req_new_update_att(map);

			}
			if (get_r == 1 ) {
				String sql3 = "Select * FROM req_bill WHERE req_title = \'" + req_bill.getReq_title()
						+ "\' AND req_bill_no = \'" + req_bill.getReq_bill_no()
						+ "\' AND req_bill_create_date = '" + set_creat_time + "';";
				logger.info("sql2 :" + sql3);
				List<Req_bill> l_q = jdbcTemplate.query(sql3, new Req_billRowMapper());
				Req_bill r = l_q.get(0);
				logger.info("r id :" + r.getId() + " " + r.getReq_bill_no() + " " + r.getReq_title());
				return_r=update_req_project_node_time(r.getId(),"1领取需求",1);
			}
			
		} catch (Exception e) {

			logger.info("Exception e :" + e.getMessage());
			return_r = false;
		}
		return return_r;

	}
	private boolean update_req_project_node_time2(Integer req_id, String node_value,java.util.Date new_date) {
		String value1="1领取需求";
		String value2="2提交需求";
		String value3="3开始开发";
		String value4="4系统测试";
		String value5="5应用测试";
		String value6="6完成测试";
		String value7="7完成上线";
		String value8="8结束跟踪";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String d=sdf.format(new_date);
		int is_insert=0;
		List rows = jdbcTemplate.queryForList("SELECT id FROM req_node_info where req_id="+req_id);
		if(rows.size()>0){
			is_insert=0;
		}else{
			is_insert=1;
		}
		if(is_insert==1){
			String sql ="";
			if(node_value.equals(value1)){
				sql = "INSERT INTO req_node_info(req_id,receive_req) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value2)){
				sql = "INSERT INTO req_node_info(req_id,submit_req) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value3)){
				sql = "INSERT INTO req_node_info(req_id,start_dev) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value4)){
				sql = "INSERT INTO req_node_info(req_id,system_test) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value5)){
				sql = "INSERT INTO req_node_info(req_id,application_testing) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value6)){
				sql = "INSERT INTO req_node_info(req_id,test_finish) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value7)){
				sql = "INSERT INTO req_node_info(req_id,online_finish) VALUES (" + req_id+",date('"+d+"'))";
			}
			if(node_value.equals(value8)){
				sql = "INSERT INTO req_node_info(req_id,trace_finish) VALUES (" + req_id+",date('"+d+"'))";
			}
			logger.info("sql :" + sql);
			int r = jdbcTemplate.update(sql);
			if(r==1){
				return true;
			}else{
				return false;
			}
		}else{
			String sql ="";
			if(node_value.equals(value1)){
				sql = "UPDATE req_node_info SET receive_req=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value2)){
				sql = "UPDATE req_node_info SET submit_req=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value3)){
				sql = "UPDATE req_node_info SET start_dev=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value4)){
				sql = "UPDATE req_node_info SET system_test=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value5)){
				sql = "UPDATE req_node_info SET application_testing=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value6)){
				sql = "UPDATE req_node_info SET test_finish=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value7)){
				sql = "UPDATE req_node_info SET online_finish=date('"+d+"') WHERE req_id=" + req_id;
			}
			if(node_value.equals(value8)){
				sql = "UPDATE req_node_info SET trace_finish=date('"+d+"') WHERE req_id=" + req_id;
			}
			
			logger.info("sql :" + sql);
			int r = jdbcTemplate.update(sql);
			if(r==1){
				return true;
			}else{
				return false;
			}
		}		
	}
	private boolean update_req_project_node_time(Integer req_id, String node_value,int is_insert) {
		String value1="1领取需求";
		String value2="2提交需求";
		String value3="3开始开发";
		String value4="4系统测试";
		String value5="5应用测试";
		String value6="6完成测试";
		String value7="7完成上线";
		String value8="8结束跟踪";
		List rows = jdbcTemplate.queryForList("SELECT id FROM req_node_info where req_id="+req_id);
		if(rows.size()>0){
			is_insert=0;
		}else{
			is_insert=1;
		}
		if(is_insert==1){
			String sql ="";
			if(node_value.equals(value1)){
				sql = "INSERT INTO req_node_info(req_id,receive_req) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value2)){
				sql = "INSERT INTO req_node_info(req_id,submit_req) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value3)){
				sql = "INSERT INTO req_node_info(req_id,start_dev) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value4)){
				sql = "INSERT INTO req_node_info(req_id,system_test) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value5)){
				sql = "INSERT INTO req_node_info(req_id,application_testing) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value6)){
				sql = "INSERT INTO req_node_info(req_id,test_finish) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value7)){
				sql = "INSERT INTO req_node_info(req_id,online_finish) VALUES (" + req_id+",now())";
			}
			if(node_value.equals(value8)){
				sql = "INSERT INTO req_node_info(req_id,trace_finish) VALUES (" + req_id+",now())";
			}
			logger.info("sql :" + sql);
			int r = jdbcTemplate.update(sql);
			if(r==1){
				return true;
			}else{
				return false;
			}
		}else{
			String sql ="";
			if(node_value.equals(value1)){
				sql = "UPDATE req_node_info SET receive_req=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value2)){
				sql = "UPDATE req_node_info SET submit_req=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value3)){
				sql = "UPDATE req_node_info SET start_dev=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value4)){
				sql = "UPDATE req_node_info SET system_test=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value5)){
				sql = "UPDATE req_node_info SET application_testing=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value6)){
				sql = "UPDATE req_node_info SET test_finish=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value7)){
				sql = "UPDATE req_node_info SET online_finish=now() WHERE req_id=" + req_id;
			}
			if(node_value.equals(value8)){
				sql = "UPDATE req_node_info SET trace_finish=now() WHERE req_id=" + req_id;
			}
			
			logger.info("sql :" + sql);
			int r = jdbcTemplate.update(sql);
			if(r==1){
				return true;
			}else{
				return false;
			}
		}
		
		
	}


	public List<Req_bill> req_search(Map<String, Object> map) {

		String g_req_title = (String) map.get("req_titile");
		String g_req_type = (String) map.get("req_type");
		String g_offer_dept = (String) map.get("offer_dept");
		String g_deffect_no = (String) map.get("deffect_no");
		String sql = "SELECT * FROM req_bill WHERE ";
		if (g_req_title != null) {
			sql = sql + " req_title like '%" + g_req_title + "%'";
		}
		if (g_req_type != null) {
			sql = sql + " AND req_type like '%" + g_req_type + "%'";
		}
		if (g_offer_dept != null) {
			sql = sql + " AND req_offer_dept like '%" + g_offer_dept + "%'";
		}
		if (g_deffect_no != null) {
			sql = sql + " AND deffect_no like '%" + g_deffect_no + "%'";
		}
		if (map.size() == 0) {
			sql = sql + " req_status in ('已接收','已分配')";
		}

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}

	}

	public List<Req_bill> req_search_for_assign(Map<String, Object> map) {

		String g_req_title = (String) map.get("req_titile");
		String g_req_type = (String) map.get("req_type");
		String g_offer_dept = (String) map.get("offer_dept");
		String g_deffect_no = (String) map.get("deffect_no");
		String g_req_bill_no = (String) map.get("req_bill_no");
		
		//String sql = "SELECT ID,req_bill_no,req_bill_original_no,req_status,req_title,req_type,req_offer_dept,req_offer,accept_date,required_completion_date,req_bill_create_date,deffect_no,return_reason FROM req_bill WHERE ";
		String sql = "SELECT * FROM req_bill WHERE ";
		if (g_req_title != null) {
			sql = sql + " req_title like '%" + g_req_title + "%'";
		}
		if (g_req_type != null) {
			sql = sql + " AND req_type like '%" + g_req_type + "%'";
		}
		if (g_offer_dept != null) {
			sql = sql + " AND req_offer_dept like '%" + g_offer_dept + "%'";
		}
		if (g_deffect_no != null) {
			sql = sql + " AND deffect_no like '%" + g_deffect_no + "%'";
		}
		if (g_req_bill_no != null) {
			sql = sql + " AND req_bill_no like '%" + g_req_bill_no + "%'";
		}
		if (map.size() == 0) {
			sql = sql + " req_status in ('已接收')";
		} else {
			sql = sql + "AND req_status in ('已接收')";
		}

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}

	}

	class Req_billRowMapper implements RowMapper<Req_bill> {
		@Override
		public Req_bill mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_bill q = new Req_bill();
			q.setId(rs.getInt("ID"));
			q.setReq_bill_no(rs.getString("req_bill_no"));
			q.setReq_type(rs.getString("req_type"));
			q.setReq_title(rs.getString("req_title"));
			q.setReq_offer_dept(rs.getString("req_offer_dept"));
			q.setDeffect_no(rs.getString("deffect_no"));
			q.setReq_status(rs.getString("req_status"));
			q.setIs_include_test_project(rs.getString("is_include_test_project"));
			String tmp = rs.getString("required_completion_date");
			
			if (tmp != null) {
				q.setRequired_completion_date(tmp);
			}
			tmp = rs.getString("accept_date");
			
			if (tmp != null) {
				q.setAccept_date(tmp);
			}
			//q.setReq_bill_create_date(rs.getTimestamp(arg0));
			q.setReq_bill_create_date(rs.getTimestamp("req_bill_create_date"));
			q.setSelect_reason(rs.getString("select_reason"));
			q.setOther_reason(rs.getString("other_reason"));
			return q;
		}

	}

	class Req_base_infoRowMapper implements RowMapper<Req_base_info> {
		@Override
		public Req_base_info mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_base_info q = new Req_base_info();
			q.setId(rs.getInt("ID"));
			q.setReq_id(rs.getInt("req_id"));
			// q.setReq_bill_no(rs.getString("req_bill_no"));
			//q.setReq_type(rs.getString("req_type"));
			// q.setReq_title(rs.getString("req_title"));
			// q.setReq_offer_dept(rs.getString("req_offer_dept"));
			q.setReq_head(rs.getString("req_head"));
			q.setReq_analyst(rs.getString("req_analyst"));
			q.setReq_supervisor(rs.getString("req_supervisor"));
			q.setReq_difficult(rs.getString("req_difficult"));
			q.setReq_orientation(rs.getString("req_orientation"));
			q.setReq_introduction(rs.getString("req_introduction"));
			q.setReq_remark(rs.getString("req_remark"));
			q.setReq_workload(rs.getFloat("req_workload"));
			q.setNode_value(rs.getString("node_value"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date d = rs.getDate("actual_finish_date");
			if (d != null) {
				q.setActual_finish_date(sdf.format(d));
			}
			d = rs.getDate("req_plan_date");
			if (d != null) {
				q.setReq_plan_date(sdf.format(d));
			}

			String s_tmp = rs.getString("technical_director");
			if (s_tmp != null) {
				q.setTechnical_director(s_tmp);
			}
			q.setCreate_time(rs.getTimestamp("create_time"));
			//q.setReq_status(rs.getString("req_status"));
			// q.setDeffect_no(rs.getString("deffect_no"));
			// q.setReq_status(rs.getString("req_status"));

			return q;
		}

	}

	public List<Req_bill> req_search(String g_id) {
		String sql = "SELECT * FROM req_bill WHERE ID ="
				+ g_id;

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public Req_bill req_bill_search_by_id(String g_id) {
		String sql = "SELECT * FROM req_bill WHERE ID ="
				+ g_id;

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q.get(0);
		} else {
			return null;
		}
	}

	public List<Req_base_info> req_base_info_search(String req_id) {
		String sql = "SELECT * FROM req_base_info WHERE req_id =" + req_id;

		logger.info("从数据库执行" + sql);
		List<Req_base_info> l_q = jdbcTemplate.query(sql, new Req_base_infoRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public Req_base_info search_req_base_info(String req_id) {
		String sql = "SELECT * FROM req_base_info WHERE req_id =" + req_id;

		logger.info("从数据库执行" + sql);
		List<Req_base_info> l_q = jdbcTemplate.query(sql, new Req_base_infoRowMapper());
		if (l_q.size() > 0) {
			return l_q.get(0);
		} else {
			return null;
		}
	}

	public boolean insert_req_base_info(Req_base_info req_base_info) {
		/*
		 * String sql = "INSERT INTO req_base_info" +
		 * "(req_id,req_orientation,req_head,req_analyst,req_supervisor,req_difficult,"
		 * +
		 * "req_workload,req_plan_date,req_introduction,req_remark,create_time,creator,"
		 * + "last_modify_time,last_modifier)VALUES(?,?,?,?,?,?,?,?,?,?,?);";
		 * logger.info("从数据库执行" + sql);
		 */
		/*
		 * if(req_bill.getDeffect_no()==null){ req_bill.setDeffect_no("");; }
		 * 
		 * if(req_bill.getReq_bill_original_no()==null){
		 * req_bill.setReq_bill_original_no(""); }
		 */
		String sql2 = "INSERT INTO req_base_info"
				+ "(req_id,req_orientation,req_head,req_analyst,req_supervisor,req_difficult,"
				+ "req_workload,req_plan_date,req_introduction,req_remark,create_time,node_value,technical_director)values(?,?,?,?,?,?,?,?,?,?,?,?,?);";
		try {
			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setLong(1, Long.parseLong(req_base_info.getReq_id()));
					ps.setString(2, req_base_info.getReq_orientation());
					ps.setString(3, req_base_info.getReq_head());
					ps.setString(4, req_base_info.getReq_analyst());
					ps.setString(5, req_base_info.getReq_supervisor());
					ps.setString(6, req_base_info.getReq_difficult());
					ps.setFloat(7, req_base_info.getReq_workload());
					ps.setDate(8, new java.sql.Date(req_base_info.getReq_plan_date().getTime()));
					ps.setString(9, req_base_info.getReq_introduction());
					ps.setString(10, req_base_info.getReq_remark());
					Date date = new java.sql.Date(new java.util.Date().getTime());
					Timestamp timeStamp = new Timestamp(date.getTime());
					ps.setTimestamp(11, timeStamp);
					ps.setString(12, req_base_info.getNode_value());
					ps.setString(13, req_base_info.getTechnical_director());
					//ps.setDate(14, new java.sql.Date(req_base_info.getActual_finish_date().getTime()));
				}
			};
			Integer get_r = jdbcTemplate.update(sql2, pss);
			if (get_r == 1) {
				//return true;
				boolean update_r = update_req_status(req_base_info.getReq_id(), "已分配");
				if (update_r) {
					return true;
				} else {
					return false;
				}

			} else {
				return false;
			}

		} catch (Exception e) {

			logger.info("Exception e :" + e.getMessage());
			return false;
		}

	}

	public boolean update_req_base_info(Req_base_info req_base_info) {
		logger.info("req_base_info : " + req_base_info.toString());
		boolean return_value = false;
		String sql = "UPDATE req_base_info SET " + "req_id = ?,req_orientation =?,"
				+ "req_workload=?,req_plan_date=?,req_introduction=?,req_remark=?,last_modify_time=?,actual_finish_date=?,technical_director=?,node_value=? WHERE id="
				+ req_base_info.getId();
		try {
			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setLong(1, Long.parseLong(req_base_info.getReq_id()));
					ps.setString(2, req_base_info.getReq_orientation());
					ps.setFloat(3, req_base_info.getReq_workload());
					ps.setDate(4, new java.sql.Date(req_base_info.getReq_plan_date().getTime()));
					ps.setString(5, req_base_info.getReq_introduction());
					ps.setString(6, req_base_info.getReq_remark());
					Date date = new java.sql.Date(new java.util.Date().getTime());
					Timestamp timeStamp = new Timestamp(date.getTime());
					ps.setTimestamp(7, timeStamp);
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					java.util.Date d = null;
					d = req_base_info.getActual_finish_date();
					if(d!=null){
						ps.setDate(8, new java.sql.Date(d.getTime()));
					}else{
						ps.setDate(8,null);
					}
					
					ps.setString(9, req_base_info.getTechnical_director());
					if(req_base_info.getNode_value()==null){
						ps.setString(10, "1领取需求");						
					}else{
						ps.setString(10, req_base_info.getNode_value());
					}
					
				}
			};

			/*
			 * String sql = "UPDATE req_base_info SET " +
			 * "req_id = "+req_base_info.getReq_id()+"," +
			 * "req_orientation ='"+req_base_info.getReq_orientation()+"'" //+
			 * ",req_head=?,req_analyst=?,req_supervisor=?,req_difficult=?," //+
			 * "req_workload=?,req_plan_date=?,req_introduction=?,req_remark=?,create_time=? "
			 * + " WHERE id="+req_base_info.getId(); try {
			 */

			logger.info("sql : " + sql);
			Integer get_r = jdbcTemplate.update(sql, pss);
			// Integer get_r = jdbcTemplate.update(sql);
			if (get_r == 1) {
				if (!req_base_info.getReq_status().equals("已分配")) {
					return_value = update_req_status(req_base_info.getReq_id(), req_base_info.getReq_status());
				} else {
					return_value = update_req_status(req_base_info.getReq_id(), "已分配");
				}
				
				return_value=update_req_project_node_time2(Integer.valueOf(req_base_info.getReq_id()),req_base_info.getNode_value(),req_base_info.getNode_value_date());
				
			}

		} catch (Exception e) {

			logger.info("Exception e :" + e.getMessage());
			return false;
		}
		return return_value;
	}

	public boolean save_req_base_info(Req_base_info req_base_info) {
		String get_req_id = req_base_info.getReq_id();
		boolean r = false;
		List<Req_base_info> list = req_base_info_search(get_req_id);

		if (list != null) {
			req_base_info.setId(list.get(0).getId());
			r = update_req_base_info(req_base_info);
			
			String atts = req_base_info.getAtt_id();
			if (atts != null && !atts.equals("")) {
				modify_req_base_info_att(get_req_id, atts);
			}
			/*if(r){
				int g_req_id=Integer.valueOf(req_base_info.getReq_id());
				String g_node=req_base_info.getNode_value();	
				if(g_node!=null){
					r=update_req_project_node_time(g_req_id,g_node,0);
				}
			}*/

		} else {
			r = insert_req_base_info(req_base_info);
			String atts = req_base_info.getAtt_id();
			if (atts != null && !atts.equals("")) {
				update_req_base_info_att(req_base_info.getReq_id(), atts);
			}
			if(r){
				int g_req_id=Integer.valueOf(req_base_info.getReq_id());
				String g_node=req_base_info.getNode_value();	
				if(g_node!=null){
					Req_bill req_bill=req_bill_search_by_id(get_req_id);
					if(req_bill.getReq_status()==null){
						r=update_req_project_node_time2(g_req_id,g_node,req_base_info.getNode_value_date());
					}else{
						r=update_req_project_node_time2(g_req_id,g_node,req_base_info.getNode_value_date());
					}
					
				}
			}
		}
		return r;

	}
	
	public boolean assign_new_req_base_info(Req_base_info req_base_info) {
		String get_req_id = req_base_info.getReq_id();
		boolean r = false;
		
			r = insert_req_base_info(req_base_info);
			String atts = req_base_info.getAtt_id();
			if (atts != null && !atts.equals("")) {
				update_req_base_info_att(req_base_info.getReq_id(), atts);
			}
		
		return r;

	}

	public boolean update_req_base_info_att(String req_id, String att_ids) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("relation_req_id", req_id);
		map.put("att_ids", att_ids);
		return att_Service.req_update_att(map);
	}
	
	public boolean modify_req_base_info_att(String req_id, String att_ids) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("relation_req_id", req_id);
		map.put("att_ids", att_ids);
		return att_Service.req_modify_update_att(map);
	}

	public boolean req_reject_save(String id, String other_reason,String select_reason) {
		if(other_reason==null){
			other_reason="";
		}
		String sql = "UPDATE req_bill  SET other_reason ='" + other_reason + "', select_reason ='" + select_reason + "',  req_status='已退回' WHERE ID =" + id;
		logger.info("从数据库执行" + sql);
		Integer get_r = jdbcTemplate.update(sql);
		if (get_r > 0) {
			return true;
		} else {
			return false;
		}

	}

	public boolean update_req_status(String req_id, String status) {
		String sql = "UPDATE req_bill  SET  req_status='" + status + "' WHERE ID =" + req_id;
		logger.info("从数据库执行" + sql);
		Integer get_r = jdbcTemplate.update(sql);
		if (get_r > 0) {
			return true;
		} else {
			return false;
		}

	}

	public List<Req_bill> maintaining_req_search(Map<String, Object> map) {
		String g_req_title = (String) map.get("req_titile");
		String g_req_type = (String) map.get("req_type");
		//String g_offer_dept = (String) map.get("req_offer_dept");
		String g_deffect_no = (String) map.get("deffect_no");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		java.util.Date g_accept_date;
		Date sqlDate = null;
		try {
			if (map.get("accept_date") != null) {
				g_accept_date = sdf.parse((String) map.get("accept_date"));
				sqlDate = new Date(g_accept_date.getTime());
			}

		} catch (ParseException e) {

			e.printStackTrace();
		}

		String g_req_bill_no = (String) map.get("req_bill_no");
		String g_req_status = (String) map.get("req_status");
		String g_req_offer_dept = (String) map.get("req_offer_dept");

		// String g_required_completion_date=;
		String sql = "SELECT * FROM req_bill WHERE ";
		String sql_where = "";
		if (g_req_title != null) {
			sql_where = " req_title like '%" + g_req_title + "%'";
		}
		if (g_req_type != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + " req_type like '%" + g_req_type + "%'";
			} else {
				sql_where = sql_where + " AND req_type like '%" + g_req_type + "%'";
			}

		}
		/*if (g_offer_dept != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + " req_offer_dept like '%" + g_offer_dept + "%'";
			} else {
				sql_where = sql_where + " AND req_offer_dept like '%" + g_offer_dept + "%'";
			}
		}*/
		if (g_deffect_no != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + " deffect_no like '%" + g_deffect_no + "%'";
			} else {
				sql_where = sql_where + " AND deffect_no like '%" + g_deffect_no + "%'";
			}

		}
		if (sqlDate != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + "  accept_date = '" + sqlDate + "'";
			} else {
				sql_where = sql_where + " AND accept_date = '" + sqlDate + "'";
			}

		}
		if (g_req_bill_no != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + "  req_bill_no like '%" + g_req_bill_no + "%'";
			} else {
				sql_where = sql_where + " AND req_bill_no like '%" + g_req_bill_no + "%'";
			}

		}
		if (g_req_status != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + "  req_status = '" + g_req_status + "'";
			} else {
				sql_where = sql_where + " AND req_status = '" + g_req_status + "'";
			}

		}
		if (g_req_offer_dept != null) {
			if (sql_where.length() < 3) {
				sql_where = sql_where + " req_offer_dept = '" + g_req_offer_dept + "'";
			} else {
				sql_where = sql_where + " AND req_offer_dept = '" + g_req_offer_dept + "'";
			}
		}

		if (sql_where.length() < 3) {
			sql = sql + " req_status not in ('a') ORDER BY ID desc";
			//sql = "SELECT * FROM req_bill ORDER BY ID desc ";
		} else {
			if(g_req_status != null){
				sql = sql + sql_where +" ORDER BY ID desc";
			}else{
				sql = sql + sql_where +" and req_status not in ('a') ORDER BY ID desc";
			}
		}

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public String get_req_status(String req_id) {
		String sql = "SELECT * FROM req_bill WHERE ID="
				+ req_id;
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		if (l_q.size() > 0) {
			return l_q.get(0).getReq_status();
		} else {
			return null;
		}

	}

	public boolean add_req_team_info_log(Map<String, Object> map) {
		String sql2 = "INSERT INTO req_team_info"
				+ "(user_name,operate,remark,role,req_id,operate_time)values(?,?,?,?,?,?);";
		logger.info("从数据库执行" + sql2);
		try {
			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, (String) map.get("user_name"));
					ps.setString(2, (String) map.get("operate"));
					ps.setString(3, (String) map.get("remark"));
					ps.setString(4, (String) map.get("role"));
					ps.setInt(5, Integer.parseInt((String) map.get("req_id")));
					Date date = new java.sql.Date(new java.util.Date().getTime());
					Timestamp timeStamp = new Timestamp(date.getTime());
					// ps.setDate(6, new java.sql.Date(new
					// java.util.Date().getTime()));
					ps.setTimestamp(6, timeStamp);
				}
			};
			Integer get_r = jdbcTemplate.update(sql2, pss);
			if (get_r == 1) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			logger.info("Exception e :" + e.getMessage());
			return false;
		}
	}

	public List<Req_team_info_log> search_req_team_info_log(String req_id) {
		String sql = "SELECT id,user_name,operate,remark,role,req_id,operate_time FROM req_team_info WHERE req_id="
				+ req_id+" ORDER BY operate_time DESC";
		logger.info("从数据库执行" + sql);
		List<Req_team_info_log> l_q = jdbcTemplate.query(sql, new Req_team_info_log_RowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	class Req_team_info_log_RowMapper implements RowMapper<Req_team_info_log> {
		@Override
		public Req_team_info_log mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_team_info_log q = new Req_team_info_log();
			q.setId(rs.getInt("ID"));
			q.setOperate(rs.getString("operate"));
			q.setOperate_time(rs.getTimestamp("operate_time"));
			q.setRemark(rs.getString("remark"));
			q.setReq_id(rs.getInt("req_id"));
			q.setRole(rs.getString("role"));
			q.setUser_name(rs.getString("user_name"));
			return q;
		}

	}

	public boolean update_req_team_info(Map<String, Object> m) {

		String sql = "UPDATE req_base_info  SET  req_head='" + (String) m.get("req_head") + "'," + "req_analyst ='"
				+ (String) m.get("req_analyst") + "',  req_supervisor ='" + (String) m.get("req_supervisor") + "' "
				+ "WHERE req_id =" + (String) m.get("req_id");
		logger.info("从数据库执行" + sql);
		Integer get_r = jdbcTemplate.update(sql);
		if (get_r > 0) {
			return true;
		} else {
			return false;
		}
	}

	public List<Req_workload_info> search_req_workload_info(String req_id) {
		String sql = "SELECT id,user_name,role,plan_complete_date,actual_complete_date,plan_workload,actual_workload,modify_datetime,req_id "
				+ "FROM req_workload_info WHERE req_id=" + req_id;
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_info_RowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	class Req_workload_info_RowMapper implements RowMapper<Req_workload_info> {
		@Override
		public Req_workload_info mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_workload_info q = new Req_workload_info();
			q.setId(rs.getInt("ID"));
			q.setReq_id(rs.getInt("req_id"));
			q.setRole(rs.getString("role"));
			q.setUser_name(rs.getString("user_name"));
			q.setPlan_complete_date(rs.getDate("plan_complete_date"));
			q.setActual_complete_date(rs.getDate("actual_complete_date"));
			q.setPlan_workload(rs.getString("plan_workload"));
			q.setActual_workload(rs.getNString("actual_workload"));
			q.setModify_datetime(rs.getTimestamp("modify_datetime"));
			return q;
		}

	}

	public Req_workload_info search_a_req_workload_info(String req_id, String user_name) {
		String sql = "SELECT id,user_name,role,plan_complete_date,actual_complete_date,plan_workload,actual_workload,modify_datetime,req_id "
				+ "FROM req_workload_info WHERE req_id=" + req_id + " AND user_name='" + user_name + "'";
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_info_RowMapper());

		if (l_q.size() > 0) {
			return l_q.get(0);
		} else {
			return null;
		}
	}
	
	public Req_workload_info search_a_req_workload_info(String req_id, String user_name,String role) {
		String sql = "SELECT id,user_name,role,plan_complete_date,actual_complete_date,plan_workload,actual_workload,modify_datetime,req_id "
				+ "FROM req_workload_info WHERE req_id=" + req_id + " AND user_name='" + user_name + "' AND role ='"+role+"'";
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_info_RowMapper());

		if (l_q.size() > 0) {
			return l_q.get(0);
		} else {
			return null;
		}
	}

	public List<Req_bill> req_search_for_test_input(Map<String, Object> map) {

		/*
		 * map.put("req_titile", s_array[0]); map.put("req_bill_no",
		 * s_array[1]); map.put("offer_dept", s_array[2]); map.put("deffect_no",
		 * s_array[3]);
		 */

		String g_req_title = (String) map.get("req_titile");
		String req_bill_no = (String) map.get("req_bill_no");
		String g_offer_dept = (String) map.get("offer_dept");
		String g_deffect_no = (String) map.get("deffect_no");
		String sql = "SELECT * FROM req_bill WHERE ID NOT in (select req_id from test_bill where test_bill.test_status in ('已接收','已分配','已完成','已退回'))  ";
		if (!g_req_title.equals("")) {
			sql = sql + " AND req_title like '%" + g_req_title + "%'";
		}
		if (!req_bill_no.equals("")) {
			sql = sql + " AND req_bill_no like '%" + req_bill_no + "%'";
		}
		if (!g_offer_dept.equals("")) {
			sql = sql + " AND req_offer_dept like '%" + g_offer_dept + "%'";
		}
		if (!g_deffect_no.equals("")) {
			sql = sql + " AND deffect_no like '%" + g_deffect_no + "%'";
		}
		/*
		 * if (map.size() == 0) { sql = sql + " req_status in ('已接收')"; } else {
		 * sql = sql + "AND req_status in ('已接收')"; }
		 */

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public List<Req_bill> get_not_accept_test_list() {
		String sql = "SELECT * FROM req_bill WHERE ID NOT IN (SELECT req_id FROM test_bill);";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		return l_q;
	}

	public boolean new_req_from_test_bill(Map<String, Object> map) {
		/*
		 * map.put("req_titile", req_titile); map.put("req_bill_no",
		 * req_bill_no);
		 */

		String req_titile = (String) map.get("req_titile");
		String req_bill_no = (String) map.get("req_bill_no");
		Timestamp tt=(Timestamp)map.get("req_bill_create_date");
		String sql = "INSERT INTO req_bill (req_bill_no,req_title,req_bill_create_date)VALUES(?,?,?); ";
		try {
			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1,req_bill_no);
					ps.setString(2, req_titile);
					ps.setTimestamp(3, tt);
				}
			};
			Integer get_r = jdbcTemplate.update(sql, pss);
			if (get_r == 1) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			logger.info("Exception e :" + e.getMessage());
			return false;
		}
	}

	public List<Req_bill> req_search_for_test_assign_grid(Map<String, Object> map) {

		String g_req_title = (String) map.get("req_titile");
		String req_bill_no = (String) map.get("req_bill_no");
		String technical_director = (String) map.get("technical_director");
		// String g_deffect_no = (String) map.get("deffect_no");
		String sql = "SELECT req_bill.* FROM req_bill LEFT JOIN req_base_info ON req_bill.ID=req_base_info.req_id  where req_bill.id IN (SELECT req_id FROM test_bill WHERE test_status='已接收')  ";
		if (!g_req_title.equals("")) {
			sql = sql + " AND req_bill.req_title like '%" + g_req_title + "%'";
		}
		if (!req_bill_no.equals("")) {
			sql = sql + " AND req_bill.req_bill_no like '%" + req_bill_no + "%'";
		}

		if (!technical_director.equals("")) {
			sql = sql + " AND req_base_info.technical_director like '%" + technical_director + "%'";
		}
		/*
		 * if (map.size() == 0) { sql = sql + " req_status in ('已接收')"; } else {
		 * sql = sql + "AND req_status in ('已接收')"; }
		 */
		
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public List<Req_bill> maintaining_req_search_my(Map<String, Object> map) {
		String g_req_title = (String) map.get("req_titile");
		String g_req_type = (String) map.get("req_type");
		String g_offer_dept = (String) map.get("req_offer_dept");
		String g_deffect_no = (String) map.get("deffect_no");
		String g_current_user = (String) map.get("current_user");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		java.util.Date g_accept_date;
		Date sqlDate = null;
		try {
			if (map.get("accept_date").equals("null") != true) {
				g_accept_date = sdf.parse((String) map.get("accept_date"));
				sqlDate = new Date(g_accept_date.getTime());
			}

		} catch (ParseException e) {

			e.printStackTrace();
		}

		String g_req_bill_no = (String) map.get("req_bill_no");
		String g_req_status = (String) map.get("req_status");
		String g_req_offer_dept = (String) map.get("req_offer_dept");

		// String g_required_completion_date=;
		String sql = "SELECT * FROM req_bill,req_base_info WHERE ";
		String sql_my = " req_bill.id=req_base_info.req_id AND (req_base_info.req_head LIKE '%" + g_current_user
				+ "%' OR req_base_info.req_analyst LIKE '%" + g_current_user
				+ "%' OR req_base_info.req_supervisor LIKE '%" + g_current_user + "%') ";
		sql = sql + sql_my;
		String sql_where = "";
		if (!g_req_title.equals("")) {
			sql_where = "AND req_title like '%" + g_req_title + "%'";
		}
		if (!g_req_type.equals("")) {

			sql_where = sql_where + " AND req_type like '%" + g_req_type + "%'";

		}
		if (!g_offer_dept.equals("")) {

			sql_where = sql_where + " AND req_offer_dept like '%" + g_offer_dept + "%'";

		}
		if (!g_deffect_no.equals("")) {

			sql_where = sql_where + " AND deffect_no like '%" + g_deffect_no + "%'";

		}
		if (sqlDate != null) {

			sql_where = sql_where + " AND accept_date = '" + sqlDate + "'";

		}
		if (!g_req_bill_no.equals("")) {

			sql_where = sql_where + " AND req_bill_no = '" + g_req_bill_no + "'";

		}
		if (!g_req_status.equals("")) {

			sql_where = sql_where + " AND req_status = '" + g_req_status + "'";

		}
		if (!g_req_offer_dept.equals("")) {

			sql_where = sql_where + " AND req_offer_dept = '" + g_req_offer_dept + "'";

		}

		// if (sql_where.length() < 3) {
		sql = sql + sql_where + " AND req_status not in ('a') ORDER BY req_bill.id Desc";
		// } else {
		// sql = sql + sql_where;
		// }

		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public boolean check_is_req_head(String name, String req_id) {
		String sql = "SELECT *  FROM req_base_info WHERE req_id=" + req_id + " AND req_head ='" + name + "'";
		logger.info("从数据库执行" + sql);
		List<Req_base_info> l_q = jdbcTemplate.query(sql, new Req_base_infoRowMapper());

		if (l_q.size() > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean req_workload_info_save(Map<String, Object> map) {
		// 判断是否已存在
		int req_id = (int) map.get("req_id");
		String g_user_name = (String) map.get("user_name");
		String g_role = (String) map.get("role");
		String g_plan_complete_date = (String) map.get("plan_complete_date");
		String g_actual_complete_date = (String) map.get("actual_complete_date");
		String g_plan_workload = (String) map.get("plan_workload");
		String g_actual_workload = (String) map.get("actual_workload");

		String sql = "SELECT * FROM req_workload_info WHERE req_id=" + req_id+" AND user_name='"+g_user_name+"' AND role ='"+g_role+"'";
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_infoRowMapper());
		try {
			if (l_q.size() > 0) {
				String set_sql = "";
				if (!g_plan_complete_date.equals("")) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					java.util.Date d_tmp;

					d_tmp = sdf.parse(g_plan_complete_date);

					Date sqlDate = null;
					sqlDate = new Date(d_tmp.getTime());

					set_sql = set_sql + "plan_complete_date = Date('" + sqlDate+"')";
				}
				if (!g_actual_complete_date.equals("")) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					java.util.Date d_tmp;
					d_tmp = sdf.parse(g_actual_complete_date);
					Date sqlDate = null;
					sqlDate = new Date(d_tmp.getTime());
					if (set_sql.length() > 3) {
						set_sql = set_sql + ", actual_complete_date = Date('" + sqlDate+"')";
					} else {
						set_sql = set_sql + "actual_complete_date =Date('" + sqlDate+"')";
					}
				}
				if (!g_plan_workload.equals("")) {
					if (set_sql.length() > 3) {
						set_sql = set_sql + ", plan_workload =" + g_plan_workload;
					} else {
						set_sql = set_sql + "plan_workload =" + g_plan_workload;
					}
				}
				if (!g_actual_workload.equals("")) {

					if (set_sql.length() > 3) {
						set_sql = set_sql + ", actual_workload =" + g_actual_workload;
					} else {
						set_sql = set_sql + "actual_workload =" + g_actual_workload;
					}
				}
				
				if(set_sql.length()>3){
					// 存在更新
					String sql2 = "UPDATE req_workload_info  SET " + set_sql + " WHERE req_id =" + req_id
							+ " AND user_name = '" + g_user_name+"' AND role ='"+g_role+"'";
					logger.info("从数据库执行" + sql);
					Integer get_r = jdbcTemplate.update(sql2);
					if (get_r > 0) {
						return true;
					} else {
						return false;
					}				
				}else{
					return true;
				}
			} else {
				// 不存在插入

				String sql3 = "INSERT INTO req_workload_info (user_name,role,plan_complete_date,actual_complete_date,plan_workload,actual_workload,req_id)VALUES(?,?,?,?,?,?,?); ";
				try {
					PreparedStatementSetter pss = new PreparedStatementSetter() {

						@Override
						public void setValues(PreparedStatement ps) throws SQLException {
							ps.setString(1, g_user_name);
							ps.setString(2, g_role);
							Date sqlDate_plan_complete_date = null;
							
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
							java.util.Date d_tmp;
							try {
								if (!g_plan_complete_date.equals("")) {

									d_tmp = sdf.parse(g_plan_complete_date);

									sqlDate_plan_complete_date = new Date(d_tmp.getTime());
								}

								Date sqlDate_actual_complete_date = null;
								if (!g_actual_complete_date.equals("")) {

									d_tmp = sdf.parse(g_actual_complete_date);

									sqlDate_actual_complete_date = new Date(d_tmp.getTime());

								}

								ps.setDate(3, sqlDate_plan_complete_date);
								ps.setDate(4, sqlDate_actual_complete_date);
								ps.setString(5, g_plan_workload);
								ps.setString(6, g_actual_workload);
								ps.setInt(7, req_id);
							} catch (ParseException e) {

								e.printStackTrace();
							}
						}
					};
					Integer get_r = jdbcTemplate.update(sql3, pss);
					if (get_r == 1) {
						return true;
					} else {
						return false;
					}

				} catch (Exception e) {
					logger.info("Exception e :" + e.getMessage());
					return false;
				}

			}
		} catch (ParseException e) {

			e.printStackTrace();
			return false;
		}

	}

	class Req_workload_infoRowMapper implements RowMapper<Req_workload_info> {
		@Override
		public Req_workload_info mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_workload_info q = new Req_workload_info();
			q.setId(rs.getInt("ID"));
			q.setUser_name(rs.getString("user_name"));
			q.setRole(rs.getString("role"));
			q.setPlan_complete_date(rs.getDate("plan_complete_date"));
			q.setActual_complete_date(rs.getDate("actual_complete_date"));
			q.setPlan_workload(rs.getString("plan_workload"));
			q.setActual_workload(rs.getString("actual_workload"));
			q.setReq_id(rs.getInt("req_id"));

			return q;
		}

	}

	public List<Req_work_log> search_req_work_log(String req_id) {
		String sql = "SELECT * FROM req_workload_log WHERE req_id=" + req_id +" order by work_date desc" ;
		logger.info("从数据库执行" + sql);
		List<Req_work_log> l_q = jdbcTemplate.query(sql, new Req_work_logRowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
		
	}
	
	public List<Req_work_log> search_req_work_log_by_name(String req_id,String name) {		
		String sql = "SELECT * FROM req_workload_log WHERE req_id=" + req_id +" AND user_name='"+name+"' order by work_date desc" ;
		logger.info("从数据库执行" + sql);
		List<Req_work_log> l_q = jdbcTemplate.query(sql, new Req_work_logRowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
		
	}
	
	class Req_work_logRowMapper implements RowMapper<Req_work_log> {
		@Override
		public Req_work_log mapRow(ResultSet rs, int rowNum) throws SQLException {
			Req_work_log q = new Req_work_log();
			q.setId(rs.getInt("ID"));
			q.setUser_name(rs.getString("user_name"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date d=rs.getDate("work_date");
			q.setWork_date(sdf.format(d));
			q.setWork_log(rs.getString("work_log"));
			q.setReq_id(rs.getInt("req_id"));

			return q;
		}

	}

	public boolean save_req_work_log(Req_work_log req_work_log) {
		// 判断req_id对应用户和日期是否存在
		boolean is_update=false;
		int req_id=req_work_log.getReq_id();
		String user=req_work_log.getUser_name();
		java.util.Date work_date=req_work_log.getWork_date();
		Date sqlDate = null;
		if(work_date!=null){
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");			
			sqlDate = new Date(work_date.getTime());
		}
		String sql = "SELECT * FROM req_workload_log WHERE req_id=" + req_id +" AND user_name='"+user+"' AND work_date= Date('" + sqlDate+"')" ;
		logger.info("从数据库执行" + sql);
		List<Req_work_log> l_q = jdbcTemplate.query(sql, new Req_work_logRowMapper());

		if (l_q.size() > 0) {
			is_update=true;
		} else {
			is_update=false;
		}
		
		if(is_update){
			//存在就更新
			Req_work_log log=l_q.get(0);
			int get_id=log.getId();
			String sql2 = "UPDATE req_workload_log  SET work_log =? WHERE id =?";
			logger.info("从数据库执行" + sql2);
			PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, req_work_log.getWork_log());
					ps.setInt(2, get_id);
					
				}
			};
			Integer get_r = jdbcTemplate.update(sql2,pss);
			if (get_r > 0) {
				return true;
			} else {
				return false;
			}				
		}else{
			//不存在就插入			
			String sql3 = "INSERT INTO req_workload_log (req_id,user_name,work_date,work_log,creator_datetime)VALUES(?,?,?,?,?); ";
			try {
				PreparedStatementSetter pss = new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps) throws SQLException {
						ps.setInt(1, req_id);
						ps.setString(2, user);
						Date sqlDate_work_date = null;
						
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						java.util.Date d_tmp;
						sqlDate_work_date = new Date(work_date.getTime());
						ps.setDate(3, sqlDate_work_date);
						ps.setString(4, req_work_log.getWork_log());
						Date date = new java.sql.Date(new java.util.Date().getTime());
						Timestamp timeStamp = new Timestamp(date.getTime());						
						ps.setTimestamp(5, timeStamp);
					}
				};
				Integer get_r = jdbcTemplate.update(sql3, pss);
				if (get_r == 1) {
					return true;
				} else {
					return false;
				}

			} catch (Exception e) {
				logger.info("Exception e :" + e.getMessage());
				return false;
			}
		}
		
	}

	public void delet_a_work_log(String id) {
		jdbcTemplate .update( "delete from req_workload_log where id="+id);
	}

	public Req_bill search_req_bill(Map<String, Object> map) {
		String req_titile = (String) map.get("req_titile");
		String req_bill_no = (String) map.get("req_bill_no");
		Timestamp tt=(Timestamp)map.get("req_bill_create_date");
		DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String tmp=sdf.format(tt);
		String sql = "SELECT * FROM req_bill WHERE req_title='" + req_titile +"' AND req_bill_no ='"+req_bill_no+"' AND req_bill_create_date='"+tmp+"'" ;
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		if (l_q.size() > 0) {
			return l_q.get(0);
		} else {
			return null;
		}
	}

	public List<Req_bill> search_all_req() {
		String sql = "SELECT * FROM req_bill ";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}
	
	public List<Req_bill> search_all_req_project() {
		String sql = "SELECT * FROM req_bill where req_status is not null ";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public double search_a_req_act_workload(Integer req_id) {
		double return_value=0.0;
		String sql = "SELECT * FROM req_workload_info WHERE req_id=" + req_id;
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_infoRowMapper());

		if (l_q.size() > 0) {
			for (Req_workload_info req_workload_info : l_q) {
				String tmp=req_workload_info.getActual_workload();
				
				if(tmp!=null){
					if(!tmp.equals("")){
						return_value=return_value+Double.parseDouble(tmp);
					}
					
				}
			}
			return return_value;
		} else {
			return 0;
		}
		
	}

	public List<Req_bill> query_req(HttpServletRequest request) {
		String accept_date_start=(String) request.getParameter("accept_date_start");
		String accept_date_end=(String) request.getParameter("accept_date_end");
		String create_time_by_req_base_info_start=(String) request.getParameter("create_time_by_req_base_info_start");
		String create_time_by_req_base_info_end=(String) request.getParameter("create_time_by_req_base_info_end");
		String required_completion_date_start=(String) request.getParameter("required_completion_date_start");
		String required_completion_date_end=(String) request.getParameter("required_completion_date_end");
		String req_plan_date_start=(String) request.getParameter("req_plan_date_start");
		String req_plan_date_end=(String) request.getParameter("req_plan_date_end");
		String actual_finish_date_start=(String) request.getParameter("actual_finish_date_start");
		String actual_finish_date_end=(String) request.getParameter("actual_finish_date_end");
		String req_expire=(String) request.getParameter("req_expire");
		String req_bill_no=(String) request.getParameter("req_bill_no");
		String req_offer_dept=(String) request.getParameter("req_offer_dept");
		String req_title=(String) request.getParameter("req_title");
		String req_type=(String) request.getParameter("req_type");
		String req_difficult=(String) request.getParameter("req_difficult");
		String deffect_no=(String) request.getParameter("deffect_no");
		String req_orientation=(String) request.getParameter("req_orientation");
		String title_key=(String) request.getParameter("title_key");
		String req_role=(String) request.getParameter("req_role");
		String req_status=(String) request.getParameter("req_status");
		//String req_workload_date_end=(String) request.getParameter("req_workload_date_end");
		String sql = "SELECT req_bill.* FROM req_bill LEFT JOIN req_base_info  ON req_bill.id= req_base_info.req_id WHERE req_bill.req_status is not null ";
		if (!accept_date_start.equals("")) {
			sql = sql + " AND req_bill.accept_date > date('"+accept_date_start+ "') ";
			if(!accept_date_end.equals("")){
				sql = sql+"  AND  req_bill.accept_date <date('" + accept_date_end + "') ";
			}				
		}
		if (!create_time_by_req_base_info_start.equals("")) {
			sql = sql + " AND req_base_info.create_time > date('"+create_time_by_req_base_info_start+ "') ";
			if(!create_time_by_req_base_info_end.equals("")){
				sql = sql+"  AND  req_base_info.create_time <date('" + create_time_by_req_base_info_end + "') ";
			}		
		}
		if (!required_completion_date_start.equals("")) {
			sql = sql + " AND req_bill.required_completion_date >  date('" + required_completion_date_start + "') ";
			if(!required_completion_date_end.equals("")){
				sql = sql+"  AND  req_bill.required_completion_date < date('" + required_completion_date_end + "') ";
			}	
		}		
		
		if (!req_plan_date_start.equals("")) {
			sql = sql + " AND req_base_info.req_plan_date >  date('" + req_plan_date_start + "') ";
			if(!req_plan_date_end.equals("")){
				sql = sql+"  AND  req_base_info.req_plan_date < date('" + req_plan_date_end + "') ";
			}	
		}
		
		
		if (!actual_finish_date_start.equals("")) {
			sql = sql + " AND req_base_info.actual_finish_date >  date('" + actual_finish_date_start + "') ";
			if(!actual_finish_date_end.equals("")){
				sql = sql+"  AND  req_base_info.actual_finish_date < date('" + actual_finish_date_end + "') ";
			}	
		}
		
		if (req_expire.equals("1")) {
			sql = sql + " AND req_bill.required_completion_date < req_base_info.actual_finish_date";
		}
		
		if (!req_bill_no.equals("")) {
			sql = sql + " AND req_bill.req_bill_no like '%"+req_bill_no+"%' ";
		}
		
		if (!req_offer_dept.equals("")) {
			sql = sql + " AND req_bill.req_offer_dept like '%"+req_offer_dept+"%' ";
		}
		
		
		if (!req_title.equals("")) {
			sql = sql + " AND req_bill.req_title like '%"+req_title+"%' ";
		}
		
		if (!req_type.equals("")) {
			sql = sql + " AND req_bill.req_type like '%"+req_type+"%' ";
		}
		
		if (!req_status.equals("")) {
			sql = sql + " AND req_bill.req_status like '%"+req_status+"%' ";
		}
		
		if (!req_difficult.equals("")) {
			sql = sql + " AND req_base_info.req_difficult like '%"+req_difficult+"%' ";
		}
		if (!deffect_no.equals("")) {
			sql = sql + " AND req_bill.deffect_no like '%"+deffect_no+"%' ";
		}
		
		if (!req_orientation.equals("")) {
			sql = sql + " AND req_base_info.req_orientation like '%"+req_orientation+"%' ";
		}
		

		if (!title_key.equals("")) {
			if(!req_role.equals("")){
				String role1="需求负责人";
				String role2="需求分析人";
				String role3="需求监理人";
				if(req_role.equals(role1)){
					sql = sql + " AND req_base_info.req_head like '%"+title_key+"%' ";
				}
				if(req_role.equals(role2)){
					sql = sql + " AND req_base_info.req_analyst like '%"+title_key+"%' ";
				}
				if(req_role.equals(role3)){
					sql = sql + " AND req_base_info.req_supervisor like '%"+title_key+"%' ";
				}
				
			}else{
				sql = sql + " AND (req_base_info.req_head like '%"+title_key+"%' OR "
						+ "req_base_info.req_analyst like '%"+title_key+"%' OR "
						+ "req_base_info.req_supervisor like '%"+title_key+"% ') ";
			}
			
		}
		
	
	
	/*	String req_workload_date_start=(String) request.getParameter("req_workload_date_start");
		String req_workload_date_end=(String) request.getParameter("req_workload_date_end");*/
		
		
		sql=sql+" ORDER BY req_bill.id DESC ";
		
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());
		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	public double get_a_req_all_of_planworkload(Integer req_id) {
		double r=0.0;
		String sql = "SELECT * "
				+ "FROM req_workload_info WHERE req_id=" + req_id ;
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_info_RowMapper());

		if (l_q.size() > 0) {
			for (int i = 0; i < l_q.size(); i++) {
				Req_workload_info e=l_q.get(i);
				
				if ( e.getPlan_workload()!=null){
					if(!e.getPlan_workload().equals("")){
						double g_workload=Double.parseDouble( e.getPlan_workload());
						r=r+g_workload;
					}
					
				}
			}
			return r;
		} else {
			return r;
		}
		
	
	}
	
	public double get_a_req_all_of_actualworkload(Integer req_id) {
		double r=0.0;
		String sql = "SELECT * "
				+ "FROM req_workload_info WHERE req_id=" + req_id ;
		logger.info("从数据库执行" + sql);
		List<Req_workload_info> l_q = jdbcTemplate.query(sql, new Req_workload_info_RowMapper());

		if (l_q.size() > 0) {
			for (int i = 0; i < l_q.size(); i++) {
				Req_workload_info e=l_q.get(i);
				
				if ( e.getActual_workload()!=null){
					if(!e.getActual_workload().equals("")){
					double g_workload=Double.parseDouble( e.getActual_workload());
					r=r+g_workload;
					}
				}
			}
			return r;
		} else {
			return r;
		}
		
	
	}

	public Req_work_log get_a_test_worklog_by_log_id(String log_id) {
		String sql = "SELECT * FROM req_workload_log WHERE id=" + log_id  ;
		logger.info("从数据库执行" + sql);
		List<Req_work_log> l_q = jdbcTemplate.query(sql, new Req_work_logRowMapper());

		if (l_q!=null) {
			return l_q.get(0);
		} else {
			return null;
		}
	}


	public int count_end_trace_project() {
		String sql = "SELECT * FROM testmgnt.req_base_info WHERE node_value='8结束跟踪'";
		logger.info("从数据库执行" + sql);
		List<Req_base_info> l_q = jdbcTemplate.query(sql, new Req_base_infoRowMapper());

		if (l_q!=null) {
			return l_q.size();
		} else {
			return 0;
		}	
		
	}


	public java.util.Date search_node_value_date_by_req_id_and_value(Integer req_id, String node_value) {
		String value1="1领取需求";
		String value2="2提交需求";
		String value3="3开始开发";
		String value4="4系统测试";
		String value5="5应用测试";
		String value6="6完成测试";
		String value7="7完成上线";
		String value8="8结束跟踪";
		
		String sql_select ="";
		String feild_str="";
			if(node_value.equals(value1)){
				sql_select = "select receive_req ";
				feild_str="receive_req";
			}
			if(node_value.equals(value2)){
				sql_select = "select submit_req ";
				feild_str="submit_req";
			}
			if(node_value.equals(value3)){
				sql_select = "select start_dev ";
				feild_str="start_dev";
			}
			if(node_value.equals(value4)){
				sql_select = "select system_test ";
				feild_str="system_test";
			}
			if(node_value.equals(value5)){
				sql_select = "select application_testing ";
				feild_str="application_testing";
			}
			if(node_value.equals(value6)){
				sql_select = "select test_finish ";
				feild_str="test_finish";
			}
			if(node_value.equals(value7)){
				sql_select = "select online_finish ";
				feild_str="online_finish";
			}
			if(node_value.equals(value8)){
				sql_select = "select trace_finish ";
				feild_str="trace_finish";
			}
		
		List rows = jdbcTemplate.queryForList(sql_select+" FROM req_node_info where req_id="+req_id);
		if(rows.size()>0){
			Map map=(Map) rows.get(0);
			Timestamp ts = (Timestamp) map.get(feild_str);   
			java.util.Date date = new java.util.Date();   
	        try {   
	            date = ts;   
	            System.out.println(date);   
	        } catch (Exception e) {   
	            e.printStackTrace();   
	        }    
			
			return date;
		}else{
			return null;
		}

	}


	public List<Req_bill> search_a_user_involved_all_req_project(String name) {
		String sql = "SELECT req_bill.* FROM req_bill,req_base_info where req_bill.req_status is not null and req_base_info.req_id=req_bill.id and"
				+ " (req_base_info.req_head like '%"+name+"%' OR req_base_info.req_analyst like '%"+name+"%' OR req_base_info.req_supervisor like '%"+name+"%') ";
		logger.info("从数据库执行" + sql);
		List<Req_bill> l_q = jdbcTemplate.query(sql, new Req_billRowMapper());

		if (l_q.size() > 0) {
			return l_q;
		} else {
			return null;
		}
	}

	
}
